-- ============================================================================
-- DATABASE EXTENSION FOR ESIMLAO PROJECT
-- Based on infigate.vn website features and existing LAOS_ESIM schema
-- Oracle Database SQL Script
-- Created: 2025-12-29
-- ============================================================================

-- ============================================================================
-- I. EXTEND EXISTING CUSTOMER_INFO TABLE FOR GMAIL LOGIN
-- ============================================================================

-- Add new columns to existing CUSTOMER_INFO table for authentication
ALTER TABLE LAOS_ESIM.CUSTOMER_INFO ADD (
    GOOGLE_ID           VARCHAR2(200),
    AVATAR_URL          VARCHAR2(1000),
    PASSWORD_HASH       VARCHAR2(500),
    STATUS              NUMBER(1) DEFAULT 1,
    IS_VERIFIED         NUMBER(1) DEFAULT 0,
    LAST_LOGIN_DATE     DATE
);

COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.GOOGLE_ID IS 'ID tu Google OAuth khi dang nhap bang Gmail';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.AVATAR_URL IS 'Duong dan anh dai dien tu Google';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.PASSWORD_HASH IS 'Mat khau ma hoa (neu dang nhap bang password)';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.STATUS IS 'Trang thai tai khoan: 0=Khoa, 1=Hoat dong';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.IS_VERIFIED IS 'Da xac thuc email: 0=Chua, 1=Da xac thuc';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.LAST_LOGIN_DATE IS 'Thoi gian dang nhap gan nhat';

-- Add comments for existing columns (if not already added)
COMMENT ON TABLE LAOS_ESIM.CUSTOMER_INFO IS 'Bang thong tin khach hang va tai khoan dang nhap';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.ID IS 'ID tu dong tang cua khach hang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.SUR_NAME IS 'Ho cua khach hang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.LAST_NAME IS 'Ten cua khach hang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.EMAIL IS 'Dia chi email (dung de dang nhap)';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.PHONE_NUMBER IS 'So dien thoai lien he';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.CREATED_DATE IS 'Ngay tao tai khoan';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_INFO.LAST_UPDATE IS 'Ngay cap nhat gan nhat';

-- Create unique index on EMAIL for login
CREATE UNIQUE INDEX LAOS_ESIM.CUSTOMER_INFO_EMAIL_UQ ON LAOS_ESIM.CUSTOMER_INFO(EMAIL);


-- ============================================================================
-- II. OTP VERIFICATION TABLE
-- ============================================================================

CREATE TABLE LAOS_ESIM.OTP_VERIFICATION (
    ID                  NUMBER(10) NOT NULL,
    CUSTOMER_ID         NUMBER(10),
    USER_EMAIL          VARCHAR2(200) NOT NULL,
    OTP_CODE            VARCHAR2(10) NOT NULL,
    OTP_TYPE            NUMBER(2) DEFAULT 1,
    EXPIRED_AT          DATE NOT NULL,
    IS_USED             NUMBER(1) DEFAULT 0,
    ATTEMPT_COUNT       NUMBER(2) DEFAULT 0,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    CONSTRAINT OTP_VERIFICATION_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.OTP_VERIFICATION IS 'Bang luu ma OTP xac thuc dang nhap';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.CUSTOMER_ID IS 'ID khach hang (CUSTOMER_INFO.ID)';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.USER_EMAIL IS 'Email can xac thuc';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.OTP_CODE IS 'Ma OTP 6 so';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.OTP_TYPE IS 'Loai OTP: 1=Dang nhap, 2=Dat lai mat khau, 3=Xac thuc email';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.EXPIRED_AT IS 'Thoi gian het han OTP';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.IS_USED IS 'Da su dung: 0=Chua, 1=Da dung';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.ATTEMPT_COUNT IS 'So lan thu sai';
COMMENT ON COLUMN LAOS_ESIM.OTP_VERIFICATION.CREATED_DATE IS 'Ngay tao OTP';

CREATE SEQUENCE LAOS_ESIM.OTP_VERIFICATION_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE INDEX LAOS_ESIM.OTP_EMAIL_IDX ON LAOS_ESIM.OTP_VERIFICATION(USER_EMAIL, IS_USED);


-- ============================================================================
-- III. TOKEN MANAGEMENT TABLE
-- ============================================================================

CREATE TABLE LAOS_ESIM.USER_TOKEN (
    ID                  NUMBER(10) NOT NULL,
    CUSTOMER_ID         NUMBER(10) NOT NULL,
    ACCESS_TOKEN        VARCHAR2(500) NOT NULL,
    REFRESH_TOKEN       VARCHAR2(500),
    TOKEN_TYPE          VARCHAR2(50) DEFAULT 'Bearer',
    DEVICE_INFO         VARCHAR2(500),
    IP_ADDRESS          VARCHAR2(50),
    EXPIRED_AT          DATE NOT NULL,
    REFRESH_EXPIRED_AT  DATE,
    IS_REVOKED          NUMBER(1) DEFAULT 0,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_USED           DATE,
    CONSTRAINT USER_TOKEN_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.USER_TOKEN IS 'Bang quan ly token xac thuc API';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.CUSTOMER_ID IS 'ID khach hang (CUSTOMER_INFO.ID)';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.ACCESS_TOKEN IS 'Access token JWT';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.REFRESH_TOKEN IS 'Refresh token de lam moi access token';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.TOKEN_TYPE IS 'Loai token (Bearer)';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.DEVICE_INFO IS 'Thong tin thiet bi dang nhap';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.IP_ADDRESS IS 'Dia chi IP khi dang nhap';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.EXPIRED_AT IS 'Thoi gian het han access token';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.REFRESH_EXPIRED_AT IS 'Thoi gian het han refresh token';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.IS_REVOKED IS 'Token bi thu hoi: 0=Khong, 1=Da thu hoi';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.CREATED_DATE IS 'Ngay tao token';
COMMENT ON COLUMN LAOS_ESIM.USER_TOKEN.LAST_USED IS 'Thoi gian su dung gan nhat';

CREATE SEQUENCE LAOS_ESIM.USER_TOKEN_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE INDEX LAOS_ESIM.USER_TOKEN_CUSTOMER_IDX ON LAOS_ESIM.USER_TOKEN(CUSTOMER_ID);
CREATE INDEX LAOS_ESIM.USER_TOKEN_ACCESS_IDX ON LAOS_ESIM.USER_TOKEN(ACCESS_TOKEN);


-- ============================================================================
-- IV. MESSAGE QUEUE TABLE (For scanning and sending messages)
-- ============================================================================

CREATE TABLE LAOS_ESIM.MESSAGE_QUEUE (
    ID                  NUMBER(10) NOT NULL,
    MESSAGE_TYPE        NUMBER(2) DEFAULT 1,
    RECIPIENT           VARCHAR2(200) NOT NULL,
    SUBJECT             NVARCHAR2(500),
    CONTENT             CLOB,
    TEMPLATE_CODE       VARCHAR2(50),
    TEMPLATE_DATA       CLOB,
    PRIORITY            NUMBER(1) DEFAULT 5,
    STATUS              NUMBER(2) DEFAULT 0,
    SCHEDULED_AT        DATE,
    PROCESSED_AT        DATE,
    RETRY_COUNT         NUMBER(2) DEFAULT 0,
    MAX_RETRY           NUMBER(2) DEFAULT 3,
    ERROR_MESSAGE       VARCHAR2(2000),
    CREATED_BY          NUMBER(10),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    CONSTRAINT MESSAGE_QUEUE_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.MESSAGE_QUEUE IS 'Bang luu tin nhan cho xu ly gui (email/SMS)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.MESSAGE_TYPE IS 'Loai tin nhan: 1=Email, 2=SMS, 3=Push Notification';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.RECIPIENT IS 'Dia chi nhan (email/so dien thoai)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.SUBJECT IS 'Tieu de tin nhan/email';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.CONTENT IS 'Noi dung tin nhan';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.TEMPLATE_CODE IS 'Ma mau tin nhan';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.TEMPLATE_DATA IS 'Du lieu thay the vao mau (JSON)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.PRIORITY IS 'Do uu tien: 1=Cao nhat, 10=Thap nhat';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.STATUS IS 'Trang thai: 0=Cho xu ly, 1=Dang xu ly, 2=Thanh cong, 3=That bai';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.SCHEDULED_AT IS 'Thoi gian dat lich gui';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.PROCESSED_AT IS 'Thoi gian da xu ly';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.RETRY_COUNT IS 'So lan thu lai';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.MAX_RETRY IS 'So lan thu lai toi da';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.ERROR_MESSAGE IS 'Thong bao loi khi gui that bai';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.CREATED_BY IS 'ID nguoi tao';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_QUEUE.CREATED_DATE IS 'Ngay tao';

CREATE SEQUENCE LAOS_ESIM.MESSAGE_QUEUE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE INDEX LAOS_ESIM.MESSAGE_QUEUE_STATUS_IDX ON LAOS_ESIM.MESSAGE_QUEUE(STATUS, PRIORITY, SCHEDULED_AT);


-- ============================================================================
-- V. MESSAGE TEMPLATE TABLE
-- ============================================================================

CREATE TABLE LAOS_ESIM.MESSAGE_TEMPLATE (
    ID                  NUMBER(10) NOT NULL,
    TEMPLATE_CODE       VARCHAR2(50) NOT NULL,
    TEMPLATE_NAME       NVARCHAR2(200) NOT NULL,
    MESSAGE_TYPE        NUMBER(2) DEFAULT 1,
    SUBJECT             NVARCHAR2(500),
    CONTENT             CLOB,
    VARIABLES           VARCHAR2(1000),
    STATUS              NUMBER(1) DEFAULT 1,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT MESSAGE_TEMPLATE_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.MESSAGE_TEMPLATE IS 'Bang mau tin nhan email/SMS';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.TEMPLATE_CODE IS 'Ma mau tin nhan (unique)';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.TEMPLATE_NAME IS 'Ten mau tin nhan';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.MESSAGE_TYPE IS 'Loai: 1=Email, 2=SMS';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.SUBJECT IS 'Tieu de email';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.CONTENT IS 'Noi dung mau';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.VARIABLES IS 'Danh sach bien (VD: {{name}},{{email}})';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.MESSAGE_TEMPLATE.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.MESSAGE_TEMPLATE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE UNIQUE INDEX LAOS_ESIM.MESSAGE_TEMPLATE_CODE_UQ ON LAOS_ESIM.MESSAGE_TEMPLATE(TEMPLATE_CODE);


-- ============================================================================
-- VI. CMS TABLES (Banner, Content, Articles - Based on infigate.vn)
-- ============================================================================

-- BANNER: Homepage banners/sliders
CREATE TABLE LAOS_ESIM.BANNER (
    ID                  NUMBER(10) NOT NULL,
    TITLE               NVARCHAR2(500),
    SUBTITLE            NVARCHAR2(1000),
    IMAGE_URL           VARCHAR2(1000) NOT NULL,
    IMAGE_MOBILE_URL    VARCHAR2(1000),
    LINK_URL            VARCHAR2(1000),
    LINK_TARGET         VARCHAR2(20) DEFAULT '_self',
    POSITION            VARCHAR2(50) DEFAULT 'HOME',
    DISPLAY_ORDER       NUMBER(5) DEFAULT 1,
    STATUS              NUMBER(1) DEFAULT 1,
    START_DATE          DATE,
    END_DATE            DATE,
    CREATED_BY          NUMBER(10),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT BANNER_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.BANNER IS 'Bang quan ly banner/slider trang web';
COMMENT ON COLUMN LAOS_ESIM.BANNER.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.BANNER.TITLE IS 'Tieu de banner';
COMMENT ON COLUMN LAOS_ESIM.BANNER.SUBTITLE IS 'Mo ta ngan';
COMMENT ON COLUMN LAOS_ESIM.BANNER.IMAGE_URL IS 'Duong dan anh banner (desktop)';
COMMENT ON COLUMN LAOS_ESIM.BANNER.IMAGE_MOBILE_URL IS 'Duong dan anh banner (mobile)';
COMMENT ON COLUMN LAOS_ESIM.BANNER.LINK_URL IS 'URL khi click vao banner';
COMMENT ON COLUMN LAOS_ESIM.BANNER.LINK_TARGET IS 'Mo tab: _self hoac _blank';
COMMENT ON COLUMN LAOS_ESIM.BANNER.POSITION IS 'Vi tri: HOME, PRODUCT, SUPPORT, etc.';
COMMENT ON COLUMN LAOS_ESIM.BANNER.DISPLAY_ORDER IS 'Thu tu hien thi';
COMMENT ON COLUMN LAOS_ESIM.BANNER.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.BANNER.START_DATE IS 'Ngay bat dau hien thi';
COMMENT ON COLUMN LAOS_ESIM.BANNER.END_DATE IS 'Ngay ket thuc hien thi';
COMMENT ON COLUMN LAOS_ESIM.BANNER.CREATED_BY IS 'ID nguoi tao';
COMMENT ON COLUMN LAOS_ESIM.BANNER.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.BANNER.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.BANNER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- CMS_CONTENT: Static pages (About us, Terms, Policies, etc.)
CREATE TABLE LAOS_ESIM.CMS_CONTENT (
    ID                  NUMBER(10) NOT NULL,
    PAGE_CODE           VARCHAR2(100) NOT NULL,
    PAGE_TITLE          NVARCHAR2(500) NOT NULL,
    PAGE_SLUG           VARCHAR2(200) NOT NULL,
    META_DESCRIPTION    NVARCHAR2(500),
    META_KEYWORDS       NVARCHAR2(500),
    CONTENT             CLOB,
    PAGE_TYPE           VARCHAR2(50) DEFAULT 'STATIC',
    STATUS              NUMBER(1) DEFAULT 1,
    LANGUAGE            VARCHAR2(10) DEFAULT 'vi',
    CREATED_BY          NUMBER(10),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    UPDATED_BY          NUMBER(10),
    CONSTRAINT CMS_CONTENT_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.CMS_CONTENT IS 'Bang noi dung cac trang tinh (Gioi thieu, Chinh sach...)';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.PAGE_CODE IS 'Ma trang (VD: ABOUT_US, TERMS, PRIVACY)';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.PAGE_TITLE IS 'Tieu de trang';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.PAGE_SLUG IS 'URL-friendly slug';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.META_DESCRIPTION IS 'Mo ta SEO';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.META_KEYWORDS IS 'Tu khoa SEO';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.CONTENT IS 'Noi dung HTML';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.PAGE_TYPE IS 'Loai trang: STATIC, POLICY, GUIDE';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.LANGUAGE IS 'Ngon ngu: vi, en, lo';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.CREATED_BY IS 'ID nguoi tao';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.LAST_UPDATE IS 'Ngay cap nhat';
COMMENT ON COLUMN LAOS_ESIM.CMS_CONTENT.UPDATED_BY IS 'ID nguoi cap nhat';

CREATE SEQUENCE LAOS_ESIM.CMS_CONTENT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE UNIQUE INDEX LAOS_ESIM.CMS_CONTENT_CODE_LANG_UQ ON LAOS_ESIM.CMS_CONTENT(PAGE_CODE, LANGUAGE);
CREATE UNIQUE INDEX LAOS_ESIM.CMS_CONTENT_SLUG_UQ ON LAOS_ESIM.CMS_CONTENT(PAGE_SLUG, LANGUAGE);


-- ============================================================================
-- VII. ARTICLE/BLOG TABLES
-- ============================================================================

-- ARTICLE_CATEGORY: Blog/Library categories
CREATE TABLE LAOS_ESIM.ARTICLE_CATEGORY (
    ID                  NUMBER(10) NOT NULL,
    CATEGORY_NAME       NVARCHAR2(200) NOT NULL,
    CATEGORY_SLUG       VARCHAR2(200) NOT NULL,
    DESCRIPTION         NVARCHAR2(1000),
    ICON_URL            VARCHAR2(500),
    PARENT_ID           NUMBER(10),
    DISPLAY_ORDER       NUMBER(5) DEFAULT 1,
    STATUS              NUMBER(1) DEFAULT 1,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT ARTICLE_CATEGORY_PK PRIMARY KEY (ID),
    CONSTRAINT ARTICLE_CATEGORY_PARENT_FK FOREIGN KEY (PARENT_ID) REFERENCES LAOS_ESIM.ARTICLE_CATEGORY(ID)
);

COMMENT ON TABLE LAOS_ESIM.ARTICLE_CATEGORY IS 'Bang danh muc bai viet (Cam nang, Huong dan)';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.CATEGORY_NAME IS 'Ten danh muc';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.CATEGORY_SLUG IS 'URL slug cua danh muc';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.DESCRIPTION IS 'Mo ta danh muc';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.ICON_URL IS 'Duong dan icon';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.PARENT_ID IS 'ID danh muc cha (null = goc)';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.DISPLAY_ORDER IS 'Thu tu hien thi';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE_CATEGORY.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.ARTICLE_CATEGORY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- ARTICLE: Blog posts/Library articles
CREATE TABLE LAOS_ESIM.ARTICLE (
    ID                  NUMBER(10) NOT NULL,
    CATEGORY_ID         NUMBER(10),
    TITLE               NVARCHAR2(500) NOT NULL,
    SLUG                VARCHAR2(500) NOT NULL,
    SUMMARY             NVARCHAR2(2000),
    CONTENT             CLOB,
    THUMBNAIL_URL       VARCHAR2(1000),
    COVER_IMAGE_URL     VARCHAR2(1000),
    META_DESCRIPTION    NVARCHAR2(500),
    META_KEYWORDS       NVARCHAR2(500),
    AUTHOR_ID           NUMBER(10),
    VIEW_COUNT          NUMBER(10) DEFAULT 0,
    IS_FEATURED         NUMBER(1) DEFAULT 0,
    IS_PINNED           NUMBER(1) DEFAULT 0,
    STATUS              NUMBER(1) DEFAULT 0,
    PUBLISHED_DATE      DATE,
    LANGUAGE            VARCHAR2(10) DEFAULT 'vi',
    CREATED_BY          NUMBER(10),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    UPDATED_BY          NUMBER(10),
    CONSTRAINT ARTICLE_PK PRIMARY KEY (ID),
    CONSTRAINT ARTICLE_CATEGORY_FK FOREIGN KEY (CATEGORY_ID) REFERENCES LAOS_ESIM.ARTICLE_CATEGORY(ID)
);

COMMENT ON TABLE LAOS_ESIM.ARTICLE IS 'Bang bai viet (Cam nang du lich, Huong dan)';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.CATEGORY_ID IS 'ID danh muc bai viet';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.TITLE IS 'Tieu de bai viet';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.SLUG IS 'URL-friendly slug';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.SUMMARY IS 'Tom tat noi dung';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.CONTENT IS 'Noi dung HTML day du';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.THUMBNAIL_URL IS 'Anh thu nho';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.COVER_IMAGE_URL IS 'Anh bia bai viet';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.META_DESCRIPTION IS 'Mo ta SEO';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.META_KEYWORDS IS 'Tu khoa SEO';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.AUTHOR_ID IS 'ID tac gia';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.VIEW_COUNT IS 'So luot xem';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.IS_FEATURED IS 'Bai viet noi bat: 0=Khong, 1=Co';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.IS_PINNED IS 'Ghim len dau: 0=Khong, 1=Co';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.STATUS IS 'Trang thai: 0=Nhap, 1=Xuat ban, 2=An';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.PUBLISHED_DATE IS 'Ngay xuat ban';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.LANGUAGE IS 'Ngon ngu: vi, en, lo';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.CREATED_BY IS 'ID nguoi tao';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.LAST_UPDATE IS 'Ngay cap nhat';
COMMENT ON COLUMN LAOS_ESIM.ARTICLE.UPDATED_BY IS 'ID nguoi cap nhat';

CREATE SEQUENCE LAOS_ESIM.ARTICLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE UNIQUE INDEX LAOS_ESIM.ARTICLE_SLUG_LANG_UQ ON LAOS_ESIM.ARTICLE(SLUG, LANGUAGE);
CREATE INDEX LAOS_ESIM.ARTICLE_CATEGORY_IDX ON LAOS_ESIM.ARTICLE(CATEGORY_ID);
CREATE INDEX LAOS_ESIM.ARTICLE_STATUS_IDX ON LAOS_ESIM.ARTICLE(STATUS, PUBLISHED_DATE);


-- ============================================================================
-- VIII. SUPPORT CENTER TABLES (Based on infigate.vn support structure)
-- ============================================================================

-- FAQ_CATEGORY: FAQ categories
CREATE TABLE LAOS_ESIM.FAQ_CATEGORY (
    ID                  NUMBER(10) NOT NULL,
    CATEGORY_NAME       NVARCHAR2(200) NOT NULL,
    CATEGORY_SLUG       VARCHAR2(200) NOT NULL,
    DESCRIPTION         NVARCHAR2(1000),
    ICON_URL            VARCHAR2(500),
    DISPLAY_ORDER       NUMBER(5) DEFAULT 1,
    STATUS              NUMBER(1) DEFAULT 1,
    LANGUAGE            VARCHAR2(10) DEFAULT 'vi',
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT FAQ_CATEGORY_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.FAQ_CATEGORY IS 'Bang danh muc FAQ (Gioi thieu, Cai dat, Su dung, Su co)';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.CATEGORY_NAME IS 'Ten danh muc (VD: Gioi thieu co ban, Cai dat & kich hoat)';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.CATEGORY_SLUG IS 'URL slug cua danh muc';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.DESCRIPTION IS 'Mo ta danh muc';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.ICON_URL IS 'Duong dan icon danh muc';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.DISPLAY_ORDER IS 'Thu tu hien thi';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.LANGUAGE IS 'Ngon ngu: vi, en, lo';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.FAQ_CATEGORY.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.FAQ_CATEGORY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- FAQ: Frequently Asked Questions
CREATE TABLE LAOS_ESIM.FAQ (
    ID                  NUMBER(10) NOT NULL,
    CATEGORY_ID         NUMBER(10),
    QUESTION            NVARCHAR2(1000) NOT NULL,
    ANSWER              CLOB,
    DISPLAY_ORDER       NUMBER(5) DEFAULT 1,
    VIEW_COUNT          NUMBER(10) DEFAULT 0,
    IS_FEATURED         NUMBER(1) DEFAULT 0,
    STATUS              NUMBER(1) DEFAULT 1,
    LANGUAGE            VARCHAR2(10) DEFAULT 'vi',
    CREATED_BY          NUMBER(10),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT FAQ_PK PRIMARY KEY (ID),
    CONSTRAINT FAQ_CATEGORY_FK FOREIGN KEY (CATEGORY_ID) REFERENCES LAOS_ESIM.FAQ_CATEGORY(ID)
);

COMMENT ON TABLE LAOS_ESIM.FAQ IS 'Bang cau hoi thuong gap';
COMMENT ON COLUMN LAOS_ESIM.FAQ.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.FAQ.CATEGORY_ID IS 'ID danh muc FAQ';
COMMENT ON COLUMN LAOS_ESIM.FAQ.QUESTION IS 'Cau hoi';
COMMENT ON COLUMN LAOS_ESIM.FAQ.ANSWER IS 'Cau tra loi (HTML)';
COMMENT ON COLUMN LAOS_ESIM.FAQ.DISPLAY_ORDER IS 'Thu tu hien thi';
COMMENT ON COLUMN LAOS_ESIM.FAQ.VIEW_COUNT IS 'So luot xem';
COMMENT ON COLUMN LAOS_ESIM.FAQ.IS_FEATURED IS 'Noi bat trang chu: 0=Khong, 1=Co';
COMMENT ON COLUMN LAOS_ESIM.FAQ.STATUS IS 'Trang thai: 0=An, 1=Hien';
COMMENT ON COLUMN LAOS_ESIM.FAQ.LANGUAGE IS 'Ngon ngu: vi, en, lo';
COMMENT ON COLUMN LAOS_ESIM.FAQ.CREATED_BY IS 'ID nguoi tao';
COMMENT ON COLUMN LAOS_ESIM.FAQ.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.FAQ.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.FAQ_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE INDEX LAOS_ESIM.FAQ_CATEGORY_IDX ON LAOS_ESIM.FAQ(CATEGORY_ID);


-- ============================================================================
-- IX. CONTACT & CUSTOMER REVIEW TABLES
-- ============================================================================

-- CONTACT_FORM: Contact form submissions
CREATE TABLE LAOS_ESIM.CONTACT_FORM (
    ID                  NUMBER(10) NOT NULL,
    FULL_NAME           NVARCHAR2(200) NOT NULL,
    EMAIL               VARCHAR2(200) NOT NULL,
    PHONE_NUMBER        VARCHAR2(20),
    SUBJECT             NVARCHAR2(500),
    MESSAGE             CLOB NOT NULL,
    STATUS              NUMBER(2) DEFAULT 0,
    ASSIGNED_TO         NUMBER(10),
    ADMIN_NOTES         NVARCHAR2(2000),
    REPLIED_AT          DATE,
    IP_ADDRESS          VARCHAR2(50),
    CREATED_DATE        DATE DEFAULT SYSDATE,
    CONSTRAINT CONTACT_FORM_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.CONTACT_FORM IS 'Bang luu form lien he tu khach hang';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.FULL_NAME IS 'Ho ten khach hang';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.EMAIL IS 'Email lien he';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.PHONE_NUMBER IS 'So dien thoai';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.SUBJECT IS 'Chu de lien he';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.MESSAGE IS 'Noi dung tin nhan';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.STATUS IS 'Trang thai: 0=Moi, 1=Dang xu ly, 2=Da tra loi, 3=Dong';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.ASSIGNED_TO IS 'ID nhan vien xu ly';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.ADMIN_NOTES IS 'Ghi chu cua admin';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.REPLIED_AT IS 'Thoi gian tra loi';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.IP_ADDRESS IS 'Dia chi IP gui form';
COMMENT ON COLUMN LAOS_ESIM.CONTACT_FORM.CREATED_DATE IS 'Ngay gui';

CREATE SEQUENCE LAOS_ESIM.CONTACT_FORM_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- CUSTOMER_REVIEW: Customer testimonials (Khach hang noi ve chung toi)
CREATE TABLE LAOS_ESIM.CUSTOMER_REVIEW (
    ID                  NUMBER(10) NOT NULL,
    CUSTOMER_NAME       NVARCHAR2(200) NOT NULL,
    AVATAR_URL          VARCHAR2(500),
    RATING              NUMBER(1) DEFAULT 5,
    REVIEW_CONTENT      NVARCHAR2(2000) NOT NULL,
    DESTINATION         NVARCHAR2(200),
    DISPLAY_ORDER       NUMBER(5) DEFAULT 1,
    IS_FEATURED         NUMBER(1) DEFAULT 0,
    STATUS              NUMBER(1) DEFAULT 0,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    APPROVED_DATE       DATE,
    APPROVED_BY         NUMBER(10),
    CONSTRAINT CUSTOMER_REVIEW_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.CUSTOMER_REVIEW IS 'Bang danh gia cua khach hang (Testimonials)';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.CUSTOMER_NAME IS 'Ten khach hang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.AVATAR_URL IS 'Anh dai dien khach hang';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.RATING IS 'Diem danh gia 1-5 sao';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.REVIEW_CONTENT IS 'Noi dung danh gia';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.DESTINATION IS 'Diem den du lich (VD: Thai Lan, Han Quoc)';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.DISPLAY_ORDER IS 'Thu tu hien thi';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.IS_FEATURED IS 'Hien thi trang chu: 0=Khong, 1=Co';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.STATUS IS 'Trang thai: 0=Cho duyet, 1=Da duyet, 2=Tu choi';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.APPROVED_DATE IS 'Ngay duyet';
COMMENT ON COLUMN LAOS_ESIM.CUSTOMER_REVIEW.APPROVED_BY IS 'ID nguoi duyet';

CREATE SEQUENCE LAOS_ESIM.CUSTOMER_REVIEW_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- ============================================================================
-- X. ADMIN USER TABLE
-- ============================================================================

CREATE TABLE LAOS_ESIM.ADMIN_USER (
    ID                  NUMBER(10) NOT NULL,
    USERNAME            VARCHAR2(100) NOT NULL,
    PASSWORD_HASH       VARCHAR2(500) NOT NULL,
    EMAIL               VARCHAR2(200) NOT NULL,
    FULL_NAME           NVARCHAR2(200),
    AVATAR_URL          VARCHAR2(500),
    ROLE                VARCHAR2(50) DEFAULT 'EDITOR',
    STATUS              NUMBER(1) DEFAULT 1,
    LAST_LOGIN_DATE     DATE,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT ADMIN_USER_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.ADMIN_USER IS 'Bang quan ly user admin/CMS';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.USERNAME IS 'Ten dang nhap';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.PASSWORD_HASH IS 'Mat khau ma hoa';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.EMAIL IS 'Email admin';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.FULL_NAME IS 'Ho va ten';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.AVATAR_URL IS 'Anh dai dien';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.ROLE IS 'Vai tro: SUPER_ADMIN, ADMIN, EDITOR, VIEWER';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.STATUS IS 'Trang thai: 0=Khoa, 1=Hoat dong';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.LAST_LOGIN_DATE IS 'Thoi gian dang nhap gan nhat';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.ADMIN_USER.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.ADMIN_USER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE UNIQUE INDEX LAOS_ESIM.ADMIN_USER_USERNAME_UQ ON LAOS_ESIM.ADMIN_USER(USERNAME);
CREATE UNIQUE INDEX LAOS_ESIM.ADMIN_USER_EMAIL_UQ ON LAOS_ESIM.ADMIN_USER(EMAIL);


-- ============================================================================
-- XI. SYSTEM CONFIGURATION TABLE
-- ============================================================================

CREATE TABLE LAOS_ESIM.SYSTEM_CONFIG (
    ID                  NUMBER(10) NOT NULL,
    CONFIG_KEY          VARCHAR2(100) NOT NULL,
    CONFIG_VALUE        NVARCHAR2(2000),
    CONFIG_TYPE         VARCHAR2(50) DEFAULT 'STRING',
    DESCRIPTION         NVARCHAR2(500),
    IS_EDITABLE         NUMBER(1) DEFAULT 1,
    CREATED_DATE        DATE DEFAULT SYSDATE,
    LAST_UPDATE         DATE,
    CONSTRAINT SYSTEM_CONFIG_PK PRIMARY KEY (ID)
);

COMMENT ON TABLE LAOS_ESIM.SYSTEM_CONFIG IS 'Bang cau hinh he thong';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.ID IS 'ID tu dong tang';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.CONFIG_KEY IS 'Khoa cau hinh (VD: SITE_NAME, HOTLINE)';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.CONFIG_VALUE IS 'Gia tri cau hinh';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.CONFIG_TYPE IS 'Loai du lieu: STRING, NUMBER, BOOLEAN, JSON';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.DESCRIPTION IS 'Mo ta cau hinh';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.IS_EDITABLE IS 'Cho phep sua: 0=Khong, 1=Co';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.CREATED_DATE IS 'Ngay tao';
COMMENT ON COLUMN LAOS_ESIM.SYSTEM_CONFIG.LAST_UPDATE IS 'Ngay cap nhat';

CREATE SEQUENCE LAOS_ESIM.SYSTEM_CONFIG_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE UNIQUE INDEX LAOS_ESIM.SYSTEM_CONFIG_KEY_UQ ON LAOS_ESIM.SYSTEM_CONFIG(CONFIG_KEY);


-- ============================================================================
-- XII. INSERT DEFAULT DATA
-- ============================================================================

-- Insert default FAQ categories (based on infigate.vn support-center)
INSERT INTO LAOS_ESIM.FAQ_CATEGORY (ID, CATEGORY_NAME, CATEGORY_SLUG, DESCRIPTION, DISPLAY_ORDER, STATUS, LANGUAGE)
VALUES (LAOS_ESIM.FAQ_CATEGORY_SEQ.NEXTVAL, N'Giới thiệu cơ bản', 'gioi-thieu-co-ban', N'Các thông tin cơ bản về eSIM', 1, 1, 'vi');

INSERT INTO LAOS_ESIM.FAQ_CATEGORY (ID, CATEGORY_NAME, CATEGORY_SLUG, DESCRIPTION, DISPLAY_ORDER, STATUS, LANGUAGE)
VALUES (LAOS_ESIM.FAQ_CATEGORY_SEQ.NEXTVAL, N'Cài đặt & kích hoạt eSIM', 'cai-dat-kich-hoat-esim', N'Hướng dẫn cài đặt và kích hoạt eSIM trên thiết bị', 2, 1, 'vi');

INSERT INTO LAOS_ESIM.FAQ_CATEGORY (ID, CATEGORY_NAME, CATEGORY_SLUG, DESCRIPTION, DISPLAY_ORDER, STATUS, LANGUAGE)
VALUES (LAOS_ESIM.FAQ_CATEGORY_SEQ.NEXTVAL, N'Cách sử dụng eSIM', 'cach-su-dung-esim', N'Hướng dẫn sử dụng eSIM hiệu quả', 3, 1, 'vi');

INSERT INTO LAOS_ESIM.FAQ_CATEGORY (ID, CATEGORY_NAME, CATEGORY_SLUG, DESCRIPTION, DISPLAY_ORDER, STATUS, LANGUAGE)
VALUES (LAOS_ESIM.FAQ_CATEGORY_SEQ.NEXTVAL, N'Các sự cố phổ biến', 'cac-su-co-pho-bien', N'Xử lý các sự cố thường gặp khi sử dụng eSIM', 4, 1, 'vi');

-- Insert default system config
INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'SITE_NAME', 'EsimLao', 'STRING', N'Tên website', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'HOTLINE', '1900 9272', 'STRING', N'Số hotline hỗ trợ', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'SUPPORT_EMAIL', 'support@esimlao.com', 'STRING', N'Email hỗ trợ', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'COMPANY_ADDRESS', N'Vientiane, Lao PDR', 'STRING', N'Địa chỉ công ty', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'OTP_EXPIRE_MINUTES', '5', 'NUMBER', N'Thời gian hết hạn OTP (phút)', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'TOKEN_EXPIRE_HOURS', '24', 'NUMBER', N'Thời gian hết hạn access token (giờ)', 1);

INSERT INTO LAOS_ESIM.SYSTEM_CONFIG (ID, CONFIG_KEY, CONFIG_VALUE, CONFIG_TYPE, DESCRIPTION, IS_EDITABLE)
VALUES (LAOS_ESIM.SYSTEM_CONFIG_SEQ.NEXTVAL, 'REFRESH_TOKEN_EXPIRE_DAYS', '30', 'NUMBER', N'Thời gian hết hạn refresh token (ngày)', 1);


-- Insert default CMS content pages
INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'ABOUT_US', N'Về chúng tôi', 'about-us', N'Thông tin về EsimLao', 'STATIC', 1, 'vi', NULL);

INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'TERMS_OF_SERVICE', N'Điều khoản dịch vụ', 'terms-and-services', N'Điều khoản sử dụng dịch vụ', 'POLICY', 1, 'vi', NULL);

INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'PRIVACY_POLICY', N'Chính sách bảo mật', 'privacy-policy', N'Chính sách bảo mật thông tin', 'POLICY', 1, 'vi', NULL);

INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'PAYMENT_POLICY', N'Chính sách thanh toán', 'payment-policy', N'Chính sách thanh toán', 'POLICY', 1, 'vi', NULL);

INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'DELIVERY_POLICY', N'Chính sách giao hàng', 'delivery-policy', N'Chính sách giao hàng', 'POLICY', 1, 'vi', NULL);

INSERT INTO LAOS_ESIM.CMS_CONTENT (ID, PAGE_CODE, PAGE_TITLE, PAGE_SLUG, META_DESCRIPTION, PAGE_TYPE, STATUS, LANGUAGE, CONTENT)
VALUES (LAOS_ESIM.CMS_CONTENT_SEQ.NEXTVAL, 'REFUND_POLICY', N'Chính sách đổi trả và hoàn tiền', 'refund-policy', N'Chính sách đổi trả và hoàn tiền', 'POLICY', 1, 'vi', NULL);


-- Insert default admin user (password: Admin@123 - should be hashed in production)
INSERT INTO LAOS_ESIM.ADMIN_USER (ID, USERNAME, PASSWORD_HASH, EMAIL, FULL_NAME, ROLE, STATUS)
VALUES (LAOS_ESIM.ADMIN_USER_SEQ.NEXTVAL, 'admin', 'CHANGE_THIS_TO_HASHED_PASSWORD', 'admin@esimlao.com', 'Administrator', 'SUPER_ADMIN', 1);


COMMIT;

-- ============================================================================
-- END OF SCRIPT
-- ============================================================================