CREATE DATABASE IF NOT EXISTS Sinao;
USE Sinao;

CREATE TABLE shop (
  shop_id BIGINT PRIMARY KEY,
  shop_name VARCHAR(255),
  shop_url VARCHAR(255),
  shop_email VARCHAR(255),
  api_key VARCHAR(255),
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE settings (
  shop_id BIGINT PRIMARY KEY,
  sinao_is_logged_in BOOLEAN,
  sinao_bearer VARCHAR(255),
  sinao_refresh_token VARCHAR(255),
  sinao_expires_in INT,
  sinao_last_refresh TIMESTAMP,
  sinao_api_key VARCHAR(255),
  shopify_order_type VARCHAR(255),
  shopify_payment_active BOOLEAN,
  shopify_payment_status VARCHAR(255),
  shopify_delivery_active BOOLEAN,
  shopify_delivery_status VARCHAR(255),
  shopify_tags_active BOOLEAN,
  shopify_tags_setting VARCHAR(255),
  shopify_tags_values TEXT, -- JSON string array, ex: '["VIP","B2B"]'
  langue VARCHAR(10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shop(shop_id)
);

CREATE TABLE subscriptions (
  shop_id BIGINT PRIMARY KEY,
  shopify_charge_id VARCHAR(255) UNIQUE,
  plan_name VARCHAR(255) NOT NULL,
  status VARCHAR(255) NOT NULL,
  price DECIMAL(10,2),
  currency VARCHAR(3) DEFAULT 'USD',
  trial_days INT DEFAULT 0,
  trial_ends_at TIMESTAMP,
  billing_on TIMESTAMP,
  cancelled_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shop(shop_id)
);

CREATE TABLE notification (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT,
  type VARCHAR(255), -- exemple "info", "warning", "error", "subscription", "billing"
  is_read BOOLEAN DEFAULT FALSE,
  page VARCHAR(255),
  title VARCHAR(255),
  content VARCHAR(1000),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shop(shop_id)
);