CREATE TABLE credentials (
 id INTEGER PRIMARY KEY NOT NULL,
 un VARCHAR(255) UNIQUE NOT NULL,
 pw CHAR(33) NOT NULL,
 email VARCHAR(255) NOT NULL
);

CREATE TABLE sessions (
 id      INTEGER PRIMARY KEY NOT NULL,
 cred_id INTEGER NOT NULL,
 sess_id CHAR(32) UNIQUE NOT NULL,
 date    INTEGER NOT NULL,
 FOREIGN KEY(cred_id) REFERENCES credentials(id)
);

CREATE TABLE devices (
 id   INTEGER PRIMARY KEY NOT NULL,
 uid  CHAR(32) UNIQUE NOT NULL,
 addr VARCHAR(255) UNIQUE NOT NULL,
 wifi CHAR(4) NOT NULL,
 name VARCHAR(50)
);

CREATE TABLE authorization (
 id   INTEGER PRIMARY KEY NOT NULL,
 c_id INTEGER NOT NULL,
 d_id INTEGER NOT NULL,
 FOREIGN KEY(c_id) REFERENCES credentials(id),
 FOREIGN KEY(d_id) REFERENCES devices(id)
);

CREATE TABLE messages (
 id      INTEGER PRIMARY KEY NOT NULL,
 d_id    INTEGER NOT NULL,
 rowid   INTEGER NOT NULL,
 date    INTEGER NOT NULL,
 contact VARCHAR(255) NOT NULL,
 type    VARCHAR(8) NOT NULL,
 datea   INTEGER,
 text    VARCHAR(255),
 FOREIGN KEY(d_id) REFERENCES devices(id)
);

CREATE table attachments (
 id      INTEGER PRIMARY KEY NOT NULL,
 m_rowid INTEGER NOT NULL,
 d_id    INTEGER NOT NULL,
 file    VARCHAR(255) NOT NULL,
 FOREIGN KEY(d_id) REFERENCES devices(id)
);

CREATE TABLE contacts (
 id      INTEGER PRIMARY KEY NOT NULL,
 d_id    INTEGER NOT NULL,
 contact VARCHAR(255) NOT NULL,
 name    VARCHAR(255) NOT NULL,
 imguri  VARCHAR(255),
 date    INTEGER NOT NULL,
 FOREIGN KEY(d_id) REFERENCES devices(id)
);

CREATE TABLE global (
 id     INTEGER PRIMARY KEY NOT NULL,
 d_id   INTEGER NOT NULL,
 key    VARCHAR(255) NOT NULL,
 value  VARCHAR(255) NOT NULL,
 FOREIGN KEY(d_id) REFERENCES devices(id)
);

    
CREATE INDEX sessions01 ON sessions(sess_id);
CREATE INDEX devices01  ON devices(uid);
CREATE INDEX messages01 ON messages(d_id);
CREATE INDEX messages02 ON messages(contact);
CREATE INDEX contacts01 ON contacts(contact);
CREATE INDEX contacts02 ON contacts(d_id);


--INSERT INTO credentials (un,pw,email) VALUES
--('username','$1$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX','user@example.com');

INSERT INTO authorization (c_id,d_id) VALUES (1,0);
INSERT INTO authorization (c_id,d_id) VALUES (1,1);

--INSERT INTO devices (uid,addr,wifi,name) VALUES
-- ('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXU',2155551212,'WIFI','Name');


