-- 001_core.sql
-- RAJAWALI CORE SYSTEM — Sprint 1 (PATCH 1)
-- SaaS foundation: tenants + licenses + locations + location_branding
--
-- Notes:
-- - All tables include tenant_id (non-negotiable).
-- - Branding is per-location (favicon + colors + title + footer).

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

-- ------------------------------------------------------------
-- tenants
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tenants (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  status TINYINT 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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- licenses
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS licenses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  license_name VARCHAR(100) NOT NULL DEFAULT 'Basic',
  location_limit INT UNSIGNED NOT NULL DEFAULT 1,
  feature_flags LONGTEXT NULL, -- JSON string
  limits LONGTEXT NULL,        -- JSON string
  is_active TINYINT 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_licenses_tenant (tenant_id),
  CONSTRAINT fk_licenses_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- locations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS locations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  address TEXT NULL,
  is_active TINYINT 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 (tenant_id),
  KEY idx_locations_active (tenant_id, is_active),
  CONSTRAINT fk_locations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- location_branding (per lokasi)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS location_branding (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  location_id INT UNSIGNED NOT NULL,
  brand_name VARCHAR(150) NOT NULL DEFAULT 'RAJAWALI',
  title VARCHAR(150) NOT NULL DEFAULT 'RAJAWALI',
  logo_path VARCHAR(255) NULL,     -- relative path: uploads/branding/...
  favicon_path VARCHAR(255) NULL,  -- relative path: uploads/branding/...
  primary_color VARCHAR(20) NULL,
  sidebar_bg VARCHAR(20) NULL,
  topbar_bg VARCHAR(20) NULL,
  footer_text VARCHAR(255) 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 uniq_location_branding (tenant_id, location_id),
  KEY idx_branding_tenant (tenant_id),
  CONSTRAINT fk_branding_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_branding_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- SEED (minimal)
-- ------------------------------------------------------------
INSERT INTO tenants (id, name, status) VALUES
  (1, 'Rajawali Printing', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), status=VALUES(status);

INSERT INTO licenses (id, tenant_id, license_name, location_limit, feature_flags, limits, is_active) VALUES
  (1, 1, 'Basic', 3,
   '{"pos":true,"warehouse":false,"spk":false,"agent":false,"payroll":false,"addon_calc":false,"report":false}',
   '{"location_limit":1}', 1)
ON DUPLICATE KEY UPDATE
  license_name=VALUES(license_name),
  location_limit=VALUES(location_limit),
  feature_flags=VALUES(feature_flags),
  limits=VALUES(limits),
  is_active=VALUES(is_active);

INSERT INTO locations (id, tenant_id, name, is_active) VALUES
  (1, 1, 'Rajawali Printing', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), is_active=VALUES(is_active);

INSERT INTO location_branding (tenant_id, location_id, brand_name, title, primary_color, sidebar_bg, topbar_bg, footer_text)
VALUES
  (1, 1, 'Rajawali Printing', 'Rajawali Printing', '#b00000', '#ffffff', '#b00000', '© Rajawali Printing 2026')
ON DUPLICATE KEY UPDATE
  brand_name=VALUES(brand_name),
  title=VALUES(title),
  primary_color=VALUES(primary_color),
  sidebar_bg=VALUES(sidebar_bg),
  topbar_bg=VALUES(topbar_bg),
  footer_text=VALUES(footer_text);
