-- Database: sub_memo
CREATE DATABASE IF NOT EXISTS sub_memo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sub_memo;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(255) NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  address TEXT NOT NULL,
  phone VARCHAR(32),
  email VARCHAR(255) NOT NULL,
  website VARCHAR(255),
  created_by_user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL UNIQUE,
  created_by_user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subscription_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  created_by_user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_plan (subscription_id, name),
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS memos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  plan_id INT NOT NULL,
  rate DECIMAL(12,2) NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  prorata DECIMAL(12,2) NOT NULL,
  notes TEXT,
  created_by_user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

INSERT INTO users (username, email, password_hash) VALUES
('dhiresh', NULL, '$2b$12$PQZxB2un8jMB1SCD8xmeXuB1h/s/bpJne2wAEIgRfloYv5Sxbj.K2'),
('darshana', NULL, '$2b$12$XEeYQL2l6EqXDYCu0wyrZOTBBGXJOH444kaJZje2EuCvjF.uxG.1W'),
('asha', NULL, '$2b$12$a/zIjpV6qVfbNlIY11CJ0uEWxLW0pta.KcE0XutKi6gDl.hWAToKe');
