DROP TABLE IF EXISTS [Category];

CREATE TABLE [Category] (
[CategoryID] TEXT  PRIMARY KEY UNIQUE NOT NULL,
[CategoryName] TEXT  NOT NULL,
[ParentID] TEXT  NOT NULL,
[LabelID] INTEGER  NOT NULL,
[CreateTime] TEXT  NULL,
[ModifyTime] TEXT  NULL,
[bSecretary] TEXT  NULL
);

DROP TABLE IF EXISTS [SecretaryCategory];


CREATE TABLE [SecretaryCategory] (
[CategoryID] TEXT  PRIMARY KEY UNIQUE NOT NULL,
[CategoryName] TEXT  NOT NULL,
[ParentID] TEXT  NOT NULL,
[LabelID] INTEGER  NOT NULL,
[CreateTime] TEXT  NULL,
[ModifyTime] TEXT  NULL,
[CategorySHA1] TEXT  NULL
);


DROP TABLE IF EXISTS [Contact];

CREATE TABLE [Contact] (
[ContactID] TEXT PRIMARY KEY UNIQUE NOT NULL,
[CategoryID] TEXT  NOT NULL,
[LabelID] INTEGER  NOT NULL,
[First_Name] TEXT  NOT NULL,
[Last_Name] TEXT  NOT NULL,
[CompanyName] TEXT  NOT NULL,
[JobTitle] TEXT  NOT NULL,
[CreatedTime] TEXT  NOT NULL,
[Favorite] BOOLEAN DEFAULT '''0''' NOT NULL,
[Status] BOOLEAN DEFAULT '''0''' NOT NULL,
[Front_Pic] TEXT  NOT NULL,
[Back_Pic] TEXT  NOT NULL,
[Index_FirstName] TEXT  NOT NULL,
[Index_LastName] TEXT  NOT NULL,
[Index_Company] TEXT  NOT NULL,
[bDelete] BOOLEAN DEFAULT '''0''' NOT NULL
);

DROP TABLE IF EXISTS [SecretaryContact];

CREATE TABLE [SecretaryContact] (
[ContactID] TEXT PRIMARY KEY UNIQUE NOT NULL,
[CategoryID] TEXT  NOT NULL,
[LabelID] INTEGER  NOT NULL,
[bDelete] BOOLEAN DEFAULT '''0''' NOT NULL
);

DROP TABLE IF EXISTS [Fields];


CREATE TABLE [Fields] (
[FieldID] INTEGER  NOT NULL,
[ContactID] TEXT  NOT NULL,
[FieldType] INTEGER  NOT NULL,
[Value] TEXT  NULL,
PRIMARY KEY ([FieldID],[ContactID])
);

DROP TABLE IF EXISTS [CardModifyAction];
CREATE TABLE [CardModifyAction] (
[ContactID] TEXT  NOT NULL,
[ModifyTime] TEXT  NULL,
[Action] INTEGER  NOT NULL,
[IsUpload] INTEGER  NOT NULL
);

DROP TABLE IF EXISTS [CardWithCategory];
CREATE TABLE [CardWithCategory] (
[ContactID] TEXT  NOT NULL,
[ModifyTime] TEXT  NULL,
[CategoryID] TEXT  NOT NULL
);

DROP TABLE IF EXISTS [CategoryModifyAction];
CREATE TABLE [CategoryModifyAction] (
[CategoryID] TEXT  NOT NULL,
[ModifyTime] TEXT  NULL,
[Action] INTEGER  NOT NULL
);

DROP TABLE IF EXISTS [CardShareAccount];

CREATE TABLE [CardShareAccount] (
[ContactID] TEXT  NOT NULL,
[ACCountID] TEXT  NULL,
[Name] TEXT  NULL,
[ModifyTime] TEXT  NULL
);

DROP TABLE IF EXISTS [Accounts];

CREATE TABLE [Accounts] (
[AccountID] TEXT  NOT NULL,
[Name] TEXT  NULL,
[Email] TEXT  NULL
);

DROP TABLE IF EXISTS [SyncFailCards];

CREATE TABLE [SyncFailCards] (
[ContactID] TEXT  NOT NULL,
[Status] INTEGER  NOT NULL,
[Note] TEXT  NULL
);

DROP TABLE IF EXISTS [Track];

CREATE TABLE [Track] (
[TrackID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[ContactID] TEXT  NOT NULL,
[Type] INTEGER  NOT NULL,
[Time] TIMESTAMP  NOT NULL
);

DROP TABLE IF EXISTS [DbInfo];

CREATE TABLE [DbInfo] (
[Version] TEXT  NOT NULL,
[DBType] TEXT  NOT NULL
);

INSERT INTO DbInfo (Version, DBType) VALUES ('1.00', 'WCT');

DROP VIEW IF EXISTS [CardView];
CREATE VIEW CardView AS
  SELECT Contact.LabelID AS LabelID,
          Contact.CategoryID AS CategoryID,
          Contact.bDelete AS bDelete,
          Contact.ContactID AS ContactID,
          Fields.FieldType,
          Fields.FieldID,
          Fields.Value
   FROM Contact,
        Fields
   WHERE Contact.ContactID = Fields.ContactID
   UNION SELECT SecretaryContact.LabelID AS LabelID,
                SecretaryContact.CategoryID AS CategoryID,
                SecretaryContact.bDelete AS bDelete,
                SecretaryContact.ContactID AS ContactID,
                Fields.FieldType,
                Fields.FieldID,
                Fields.Value
   FROM SecretaryContact,
        Fields
   WHERE SecretaryContact.ContactID = Fields.ContactID;





