-- drop invoices and clients table if they exist
DROP TABLE IF EXISTS Invoices;
DROP TABLE IF EXISTS Clients;

CREATE TABLE Clients(
  id integer PRIMARY KEY auto_increment,
  -- user info
  username varchar(100) NOT NULL UNIQUE,
  password varchar (100) NOT NULL,
  email varchar(256) NOT NULL UNIQUE,
  phone varchar(20),
  country varchar(10),
  postbox varchar(30),
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- user preferences
  email_notification boolean DEFAULT FALSE,
  sms_notification boolean DEFAULT FALSE,
  -- subscription info
  timing varchar(1024), -- json with the check times
  plan integer DEFAULT 0,
  subscription_end timestamp DEFAULT CURRENT_TIMESTAMP,
  -- device info
  mac varchar(100),
  testing boolean DEFAULT FALSE,
  deployed boolean DEFAULT FALSE,
  wifi_rssi varchar(10),
  battery varchar(10),
  latest_image varchar(256),
  latest_image_time timestamp DEFAULT CURRENT_TIMESTAMP,
  -- device settings
  continuous_check boolean DEFAULT FALSE, -- if true, device will check for new mail even if change detected
  photo_interval integer DEFAULT 10080, -- for use by the esp32, sleep interval till next_check
  next_check integer DEFAULT 0, -- time to wait before next check in minutes
  door_wait integer DEFAULT 5, -- time to wait after door open in seconds
  heartbeat integer DEFAULT 20160, -- time to wait before sending heartbeat in minutes
  -- reference image settings
  has_qr boolean DEFAULT FALSE,
  qr_random varchar(10) UNIQUE,
  has_ref_img boolean DEFAULT FALSE,
  ref_img varchar(256),
  center_square_x varchar(50),
  center_square_y varchar(50),
  center_square_w varchar(50),
  center_square_h varchar(50)
);

CREATE TABLE Invoices (
  payment_id integer PRIMARY KEY auto_increment,
  dateofPayment timestamp,
  method integer,
  amount integer,
  product integer,
  receiptId integer,
  confirmed boolean,
  user_id varchar (100),
  uid integer
);

ALTER TABLE Invoices ADD FOREIGN KEY (uid) REFERENCES Clients (id);
