-- RAJAWALI CORE SYSTEM
-- Sprint 1 / Patch 1: SaaS Foundation (Tenant, License, Location, Branding)
-- MySQL 8+ (utf8mb4)

SET NAMES utf8mb4;
SET time_zone = '+07:00';

-- =========================
-- TABLE: tenants
-- =========================
CREATE TABLE IF NOT EXISTS tenants (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================
-- TABLE: licenses
-- =========================
CREATE TABLE IF NOT EXISTS licenses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  plan VARCHAR(50) NOT NULL DEFAULT 'Basic',
  feature_flags JSON NULL,
  location_limit INT NOT NULL DEFAULT 1,
  started_at DATE NULL,
  ended_at DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_licenses_tenant_id (tenant_id),
  CONSTRAINT fk_licenses_tenant
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================
-- TABLE: locations
-- =========================
CREATE TABLE IF NOT EXISTS locations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_locations_tenant_id (tenant_id),
  CONSTRAINT fk_locations_tenant
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================
-- TABLE: location_branding
-- minimal: logo, favicon, warna, title, footer
-- contract: brand_name + title + logo + favicon + primary + sidebar_bg + topbar_bg + footer_text
-- =========================
CREATE TABLE IF NOT EXISTS location_branding (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  location_id INT UNSIGNED NOT NULL,
  brand_name VARCHAR(120) NULL,
  title VARCHAR(180) NULL,
  logo VARCHAR(255) NULL,
  favicon VARCHAR(255) NULL,
  primary_color VARCHAR(24) NULL,
  sidebar_bg VARCHAR(24) NULL,
  topbar_bg VARCHAR(24) NULL,
  footer_text VARCHAR(180) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_location_branding_location_id (location_id),
  CONSTRAINT fk_branding_location
    FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================
-- SEEDER MINIMAL (idempotent)
-- 1 tenant + 1 license + 1 lokasi + branding default
-- =========================

INSERT INTO tenants (id, name, status)
SELECT 1, 'Default Tenant', 'active'
WHERE NOT EXISTS (SELECT 1 FROM tenants WHERE id = 1);

INSERT INTO licenses (id, tenant_id, plan, feature_flags, location_limit, started_at)
SELECT 1, 1, 'Basic',
       JSON_OBJECT('branding', true, 'multi_location', true),
       2,
       CURDATE()
WHERE NOT EXISTS (SELECT 1 FROM licenses WHERE id = 1);

INSERT INTO locations (id, tenant_id, name, is_active)
SELECT 1, 1, 'Rajawali Printing 1', 1
WHERE NOT EXISTS (SELECT 1 FROM locations WHERE id = 1);

INSERT INTO location_branding (id, location_id, brand_name, title, primary_color, sidebar_bg, topbar_bg, footer_text)
SELECT 1, 1, 'RAJAWALI', 'RAJAWALI', '#b00000', '#ffffff', '#b00000', '© RAJAWALI'
WHERE NOT EXISTS (SELECT 1 FROM location_branding WHERE id = 1);
