CREATE DATABASE IF NOT EXISTS cafepro CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cafepro;

CREATE TABLE IF NOT EXISTS tenants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_code VARCHAR(50) NOT NULL UNIQUE,
 business_name VARCHAR(150) NOT NULL,
 owner_name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(30) NULL,
 slug VARCHAR(100) NOT NULL UNIQUE,
 status ENUM('trial','active','suspended','expired') NOT NULL DEFAULT 'trial',
 trial_ends_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 plan_code VARCHAR(40) NOT NULL UNIQUE,
 plan_name VARCHAR(100) NOT NULL,
 price_monthly DECIMAL(15,2) NOT NULL DEFAULT 0,
 price_yearly DECIMAL(15,2) NOT NULL DEFAULT 0,
 max_outlets INT UNSIGNED NOT NULL DEFAULT 1,
 max_users INT UNSIGNED NOT NULL DEFAULT 3,
 features JSON NULL,
 status TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 billing_cycle ENUM('monthly','yearly') NOT NULL DEFAULT 'monthly',
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 status ENUM('trial','active','past_due','cancelled','expired') NOT NULL DEFAULT 'trial',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_sub_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
 CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id),
 INDEX idx_sub_tenant_status (tenant_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS outlets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NOT NULL,
 outlet_code VARCHAR(50) NOT NULL,
 outlet_name VARCHAR(150) NOT NULL,
 address TEXT NULL,
 phone VARCHAR(30) NULL,
 timezone VARCHAR(50) NOT NULL DEFAULT 'Asia/Jakarta',
 status TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_outlet_code (tenant_id,outlet_code),
 CONSTRAINT fk_outlet_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
 INDEX idx_outlet_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NULL,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 status TINYINT(1) NOT NULL DEFAULT 1,
 last_login DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_user_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
 INDEX idx_user_tenant (tenant_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NULL,
 role_code VARCHAR(50) NOT NULL,
 role_name VARCHAR(100) NOT NULL,
 description VARCHAR(255) NULL,
 UNIQUE KEY uq_role_tenant_code (tenant_id,role_code),
 CONSTRAINT fk_role_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 permission_code VARCHAR(100) NOT NULL UNIQUE,
 permission_name VARCHAR(150) NOT NULL,
 module_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role_id BIGINT UNSIGNED NOT NULL,
 permission_id BIGINT UNSIGNED NOT NULL,
 UNIQUE KEY uq_role_permission (role_id,permission_id),
 CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id),
 CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 role_id BIGINT UNSIGNED NOT NULL,
 outlet_id BIGINT UNSIGNED NULL,
 CONSTRAINT fk_ur_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id),
 CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id),
 CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id),
 CONSTRAINT fk_ur_outlet FOREIGN KEY (outlet_id) REFERENCES outlets(id),
 UNIQUE KEY uq_user_role_scope (tenant_id,user_id,role_id,outlet_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 scope ENUM('platform','tenant','outlet') NOT NULL,
 tenant_id BIGINT UNSIGNED NULL,
 setting_key VARCHAR(100) NOT NULL,
 setting_value LONGTEXT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_setting_scope (scope,tenant_id,setting_key),
 CONSTRAINT fk_setting_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tenant_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 description TEXT NULL,
 ip_address VARCHAR(45) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_audit_tenant_date (tenant_id,created_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO plans (plan_code,plan_name,price_monthly,price_yearly,max_outlets,max_users,features)
VALUES
('starter','Starter',99000,990000,1,3,'{"pos":true,"qr_menu":true}'),
('professional','Professional',249000,2490000,3,15,'{"pos":true,"qr_menu":true,"inventory":true,"kitchen":true}'),
('enterprise','Enterprise',499000,4990000,999,999,'{"all":true}');

INSERT IGNORE INTO permissions (permission_code,permission_name,module_name) VALUES
('dashboard.view','Lihat dashboard','Dashboard'),
('outlets.manage','Kelola outlet','Outlet'),
('branding.manage','Kelola branding cafe','Settings'),
('pos.access','Akses kasir','POS'),
('orders.manage','Kelola pesanan','Orders'),
('reports.view','Lihat laporan','Reports'),
('users.manage','Kelola pengguna','Users');