-- ============================================================================
-- ORACLE CONNECTOR TEST SCHEMA
-- Each test case uses a separate set of tables to avoid accumulated effects
-- ============================================================================

-- ============================================================================
-- CASE 1: CREATE TABLE with PRIMARY KEY
-- ============================================================================
CREATE TABLE pk_simple (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL
);

-- ============================================================================
-- CASE 2: CREATE TABLE with SELF-REFERENCING FOREIGN KEY
-- ============================================================================
CREATE TABLE self_ref_fk (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id NUMBER,
  name VARCHAR2(100) NOT NULL,
  CONSTRAINT fk_self_ref FOREIGN KEY (parent_id) REFERENCES self_ref_fk(id) ON DELETE SET NULL
);

-- ============================================================================
-- CASE 3: CREATE TABLE with NON-SELF-REFERENCING FOREIGN KEY
-- ============================================================================
CREATE TABLE fk_parent (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL
);

CREATE TABLE fk_child (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id NUMBER NOT NULL,
  description VARCHAR2(200),
  CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES fk_parent(id) ON DELETE CASCADE
);

-- ============================================================================
-- CASE 4: CREATE TABLE with CHECK CONSTRAINTS
-- ============================================================================
CREATE TABLE check_constraints (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  age NUMBER NOT NULL,
  salary NUMBER(10, 2) NOT NULL,
  status VARCHAR2(20) NOT NULL,
  start_date DATE,
  end_date DATE,
  CONSTRAINT chk_age_positive CHECK (age > 0),
  CONSTRAINT chk_salary_range CHECK (salary BETWEEN 0 AND 1000000),
  CONSTRAINT chk_status_values CHECK (status IN ('active', 'inactive', 'pending')),
  CONSTRAINT chk_date_range CHECK (start_date <= end_date)
);

-- ============================================================================
-- CASE 5: CREATE TABLE with DEFAULT VALUES
-- ============================================================================
CREATE TABLE default_values (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  str_default VARCHAR2(50) DEFAULT 'default_string',
  num_default NUMBER DEFAULT 42,
  decimal_default NUMBER(10, 2) DEFAULT 99.99,
  date_default DATE DEFAULT SYSDATE,
  timestamp_default TIMESTAMP DEFAULT SYSTIMESTAMP,
  char_default CHAR(10) DEFAULT 'fixed'
);

-- ============================================================================
-- CASE 6: CREATE TABLE with NOT NULL columns
-- ============================================================================
CREATE TABLE not_null_cols (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  required_str VARCHAR2(100) NOT NULL,
  required_num NUMBER NOT NULL,
  required_date DATE NOT NULL
);

-- ============================================================================
-- CASE 7: CREATE TABLE with NULLABLE columns
-- ============================================================================
CREATE TABLE nullable_cols (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  optional_str VARCHAR2(100),
  optional_num NUMBER,
  optional_date DATE
);

-- ============================================================================
-- CASE 8: CREATE TABLE with AUTOINCREMENT (IDENTITY)
-- ============================================================================
CREATE TABLE autoincrement_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL
);

-- ============================================================================
-- CASE 9: CREATE TABLE with UNIQUE constraint
-- ============================================================================
CREATE TABLE unique_cols (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR2(100) NOT NULL UNIQUE,
  username VARCHAR2(50) NOT NULL,
  CONSTRAINT uq_username UNIQUE (username)
);

-- ============================================================================
-- CASE 10: CREATE TABLE with COMPOSITE PRIMARY KEY
-- ============================================================================
CREATE TABLE composite_pk (
  region_id NUMBER NOT NULL,
  product_id NUMBER NOT NULL,
  quantity NUMBER DEFAULT 0,
  price NUMBER(10, 2),
  CONSTRAINT pk_composite PRIMARY KEY (region_id, product_id)
);

-- ============================================================================
-- CASE 11: CREATE TABLE with COMPOSITE SELF-REFERENCING FOREIGN KEY
-- ============================================================================
CREATE TABLE composite_self_ref (
  dept_id NUMBER NOT NULL,
  emp_id NUMBER NOT NULL,
  parent_dept_id NUMBER,
  parent_emp_id NUMBER,
  name VARCHAR2(100) NOT NULL,
  CONSTRAINT pk_composite_self_ref PRIMARY KEY (dept_id, emp_id),
  CONSTRAINT fk_composite_self FOREIGN KEY (parent_dept_id, parent_emp_id)
    REFERENCES composite_self_ref(dept_id, emp_id) ON DELETE SET NULL
);

-- ============================================================================
-- CASE 12: CREATE TABLE with COMPOSITE UNIQUE constraint
-- ============================================================================
CREATE TABLE composite_unique (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100),
  CONSTRAINT uq_full_name UNIQUE (first_name, last_name)
);

-- ============================================================================
-- CASE 13: FUNCTIONAL INDEX
-- ============================================================================
CREATE TABLE functional_index_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  full_name VARCHAR2(100) NOT NULL,
  email VARCHAR2(100) NOT NULL
);

CREATE INDEX idx_lower_name ON functional_index_table (LOWER(full_name));
CREATE INDEX idx_upper_email ON functional_index_table (UPPER(email));

-- ============================================================================
-- CASE 14: COMPOSITE INDEX
-- ============================================================================
CREATE TABLE composite_index_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  department VARCHAR2(50)
);

CREATE INDEX idx_composite_name ON composite_index_table (first_name, last_name);
CREATE INDEX idx_composite_dept ON composite_index_table (department, last_name);

-- ============================================================================
-- CASE 15: MIXED FUNCTIONAL AND COLUMN INDEX
-- ============================================================================
CREATE TABLE mixed_index_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100)
);

CREATE INDEX idx_mixed_name ON mixed_index_table (LOWER(first_name), last_name);
CREATE INDEX idx_mixed_email ON mixed_index_table (first_name, UPPER(email));

-- ============================================================================
-- CASE 16: COLUMN INDEX (simple)
-- ============================================================================
CREATE TABLE column_index_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category VARCHAR2(50),
  status VARCHAR2(20),
  created_at DATE DEFAULT SYSDATE
);

CREATE INDEX idx_category ON column_index_table (category);
CREATE INDEX idx_status ON column_index_table (status);
CREATE INDEX idx_created_at ON column_index_table (created_at);

-- ============================================================================
-- CASE 17: ALTER TABLE to ADD PRIMARY KEY
-- ============================================================================
CREATE TABLE alter_add_pk (
  id NUMBER NOT NULL,
  name VARCHAR2(100)
);

ALTER TABLE alter_add_pk ADD CONSTRAINT pk_alter_pk PRIMARY KEY (id);

-- ============================================================================
-- CASE 18: ALTER TABLE to ADD FOREIGN KEY (non-self-ref)
-- ============================================================================
CREATE TABLE alter_fk_parent (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

CREATE TABLE alter_fk_child (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id NUMBER
);

ALTER TABLE alter_fk_child ADD CONSTRAINT fk_alter_parent
  FOREIGN KEY (parent_id) REFERENCES alter_fk_parent(id) ON DELETE CASCADE;

-- ============================================================================
-- CASE 19: ALTER TABLE to ADD SELF-REFERENCING FOREIGN KEY
-- ============================================================================
CREATE TABLE alter_self_ref (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id NUMBER,
  name VARCHAR2(100)
);

ALTER TABLE alter_self_ref ADD CONSTRAINT fk_alter_self
  FOREIGN KEY (parent_id) REFERENCES alter_self_ref(id) ON DELETE SET NULL;

-- ============================================================================
-- CASE 20: ALTER TABLE to ADD CHECK CONSTRAINT
-- ============================================================================
CREATE TABLE alter_add_check (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  age NUMBER,
  salary NUMBER(10, 2),
  status VARCHAR2(20)
);

ALTER TABLE alter_add_check ADD CONSTRAINT chk_alter_age CHECK (age >= 18);
ALTER TABLE alter_add_check ADD CONSTRAINT chk_alter_salary CHECK (salary > 0);
ALTER TABLE alter_add_check ADD CONSTRAINT chk_alter_status CHECK (status IN ('A', 'B', 'C'));

-- ============================================================================
-- CASE 21: ALTER TABLE to ADD UNIQUE CONSTRAINT
-- ============================================================================
CREATE TABLE alter_add_unique (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR2(100),
  code VARCHAR2(20)
);

ALTER TABLE alter_add_unique ADD CONSTRAINT uq_alter_email UNIQUE (email);
ALTER TABLE alter_add_unique ADD CONSTRAINT uq_alter_code UNIQUE (code);

-- ============================================================================
-- CASE 22: ALTER TABLE to ADD COMPOSITE PRIMARY KEY
-- ============================================================================
CREATE TABLE alter_composite_pk (
  region_id NUMBER NOT NULL,
  product_id NUMBER NOT NULL,
  quantity NUMBER
);

ALTER TABLE alter_composite_pk ADD CONSTRAINT pk_alter_composite PRIMARY KEY (region_id, product_id);

-- ============================================================================
-- CASE 23: ALTER TABLE to ADD COMPOSITE FOREIGN KEY
-- ============================================================================
CREATE TABLE alter_composite_fk_parent (
  dept_id NUMBER NOT NULL,
  emp_id NUMBER NOT NULL,
  name VARCHAR2(100),
  CONSTRAINT pk_alter_comp_parent PRIMARY KEY (dept_id, emp_id)
);

CREATE TABLE alter_composite_fk_child (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ref_dept_id NUMBER,
  ref_emp_id NUMBER
);

ALTER TABLE alter_composite_fk_child ADD CONSTRAINT fk_alter_composite
  FOREIGN KEY (ref_dept_id, ref_emp_id) REFERENCES alter_composite_fk_parent(dept_id, emp_id);

-- ============================================================================
-- CASE 24: ALTER TABLE to ADD COMPOSITE UNIQUE
-- ============================================================================
CREATE TABLE alter_composite_unique (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50)
);

ALTER TABLE alter_composite_unique ADD CONSTRAINT uq_alter_fullname UNIQUE (first_name, last_name);

-- ============================================================================
-- CASE 25: ALTER TABLE ADD COLUMN
-- ============================================================================
CREATE TABLE alter_add_column (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

ALTER TABLE alter_add_column ADD new_col VARCHAR2(50);
ALTER TABLE alter_add_column ADD another_col NUMBER DEFAULT 0;
ALTER TABLE alter_add_column ADD date_col DATE DEFAULT SYSDATE;

-- ============================================================================
-- CASE 26: INDEX - BTREE (default in Oracle)
-- ============================================================================
CREATE TABLE idx_btree_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  code VARCHAR2(20),
  category VARCHAR2(50)
);

CREATE INDEX idx_btree_code ON idx_btree_table (code);

-- ============================================================================
-- CASE 27: INDEX - BITMAP
-- ============================================================================
CREATE TABLE idx_bitmap_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  gender VARCHAR2(10),
  status VARCHAR2(20)
);

CREATE BITMAP INDEX idx_bitmap_gender ON idx_bitmap_table (gender);
CREATE BITMAP INDEX idx_bitmap_status ON idx_bitmap_table (status);

-- ============================================================================
-- CASE 28: INDEX - UNIQUE
-- ============================================================================
CREATE TABLE idx_unique_table (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR2(100),
  ssn VARCHAR2(20)
);

CREATE UNIQUE INDEX idx_unique_email ON idx_unique_table (email);
CREATE UNIQUE INDEX idx_unique_ssn ON idx_unique_table (ssn);

-- ============================================================================
-- CASE 29: INDEX - COMPOSITE
-- ============================================================================
CREATE TABLE idx_composite_only (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  col1 VARCHAR2(50),
  col2 VARCHAR2(50),
  col3 VARCHAR2(50)
);

CREATE INDEX idx_comp_12 ON idx_composite_only (col1, col2);
CREATE INDEX idx_comp_23 ON idx_composite_only (col2, col3);
CREATE INDEX idx_comp_123 ON idx_composite_only (col1, col2, col3);

-- ============================================================================
-- CASE 30: INDEX - FUNCTIONAL (via ALTER/separate CREATE)
-- ============================================================================
CREATE TABLE idx_functional_only (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  full_name VARCHAR2(100),
  description VARCHAR2(500)
);

CREATE INDEX idx_func_lower_name ON idx_functional_only (LOWER(full_name));
CREATE INDEX idx_func_substr ON idx_functional_only (SUBSTR(description, 1, 100));

-- ============================================================================
-- CASE 31: ADD REFS (SIMPLE) - via ALTER TABLE
-- ============================================================================
CREATE TABLE ref_simple_parent (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

CREATE TABLE ref_simple_child (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id NUMBER
);

ALTER TABLE ref_simple_child ADD CONSTRAINT fk_ref_simple
  FOREIGN KEY (parent_id) REFERENCES ref_simple_parent(id);

-- ============================================================================
-- CASE 32: ADD REFS (COMPOSITE) - via ALTER TABLE
-- ============================================================================
CREATE TABLE ref_composite_parent (
  key1 NUMBER NOT NULL,
  key2 NUMBER NOT NULL,
  data VARCHAR2(100),
  CONSTRAINT pk_ref_comp_parent PRIMARY KEY (key1, key2)
);

CREATE TABLE ref_composite_child (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  fk1 NUMBER,
  fk2 NUMBER
);

ALTER TABLE ref_composite_child ADD CONSTRAINT fk_ref_composite
  FOREIGN KEY (fk1, fk2) REFERENCES ref_composite_parent(key1, key2);

-- ============================================================================
-- CASE 33: Various Oracle data types
-- ============================================================================
CREATE TABLE oracle_data_types (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  -- Character types
  char_col CHAR(10),
  varchar2_col VARCHAR2(100),
  nchar_col NCHAR(10),
  nvarchar2_col NVARCHAR2(100),
  -- Number types
  number_col NUMBER,
  number_precision NUMBER(10),
  number_scale NUMBER(10, 2),
  binary_float_col BINARY_FLOAT,
  binary_double_col BINARY_DOUBLE,
  -- Date/Time types
  date_col DATE,
  timestamp_col TIMESTAMP,
  timestamp_tz TIMESTAMP WITH TIME ZONE,
  timestamp_ltz TIMESTAMP WITH LOCAL TIME ZONE,
  interval_ym INTERVAL YEAR TO MONTH,
  interval_ds INTERVAL DAY TO SECOND,
  -- Large object types
  clob_col CLOB,
  nclob_col NCLOB,
  blob_col BLOB,
  -- Raw type
  raw_col RAW(100),
  -- ROWID
  rowid_col ROWID
);

-- ============================================================================
-- CASE 34: Table with comments
-- ============================================================================
CREATE TABLE table_with_comments (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  description VARCHAR2(500)
);

COMMENT ON TABLE table_with_comments IS 'This table''
stores items with descriptions';
COMMENT ON COLUMN table_with_comments.id IS 'Unique
identifier';
COMMENT ON COLUMN table_with_comments.name IS '!@#$%^&*()[]"Item name - required field';
COMMENT ON COLUMN table_with_comments.description IS 'Optional description of the item';

-- ============================================================================
-- CASE 35: Composite self-ref FK added via ALTER
-- ============================================================================
CREATE TABLE alter_comp_self_ref (
  dept_id NUMBER NOT NULL,
  emp_id NUMBER NOT NULL,
  parent_dept_id NUMBER,
  parent_emp_id NUMBER,
  name VARCHAR2(100),
  CONSTRAINT pk_alter_comp_self PRIMARY KEY (dept_id, emp_id)
);

ALTER TABLE alter_comp_self_ref ADD CONSTRAINT fk_alter_comp_self
  FOREIGN KEY (parent_dept_id, parent_emp_id) REFERENCES alter_comp_self_ref(dept_id, emp_id);

-- ============================================================================
-- CASE 36: Special data types with precision
-- ============================================================================
CREATE TABLE special_data_types (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  -- Timestamp with various precisions
  ts_default TIMESTAMP,
  ts_0 TIMESTAMP(0),
  ts_3 TIMESTAMP(3),
  ts_6 TIMESTAMP(6),
  ts_9 TIMESTAMP(9),
  -- Timestamp with time zone with various precisions
  ts_tz_default TIMESTAMP WITH TIME ZONE,
  ts_tz_0 TIMESTAMP(0) WITH TIME ZONE,
  ts_tz_3 TIMESTAMP(3) WITH TIME ZONE,
  ts_tz_6 TIMESTAMP(6) WITH TIME ZONE,
  ts_tz_9 TIMESTAMP(9) WITH TIME ZONE,
  -- Timestamp with local time zone with various precisions
  ts_ltz_default TIMESTAMP WITH LOCAL TIME ZONE,
  ts_ltz_0 TIMESTAMP(0) WITH LOCAL TIME ZONE,
  ts_ltz_3 TIMESTAMP(3) WITH LOCAL TIME ZONE,
  ts_ltz_6 TIMESTAMP(6) WITH LOCAL TIME ZONE,
  ts_ltz_9 TIMESTAMP(9) WITH LOCAL TIME ZONE,
  -- Interval year to month with various precisions
  iym_default INTERVAL YEAR TO MONTH,
  iym_2 INTERVAL YEAR(2) TO MONTH,
  iym_4 INTERVAL YEAR(4) TO MONTH,
  iym_9 INTERVAL YEAR(9) TO MONTH,
  -- Interval day to second with various precisions
  ids_default INTERVAL DAY TO SECOND,
  ids_day_4 INTERVAL DAY(4) TO SECOND,
  ids_sec_3 INTERVAL DAY TO SECOND(3),
  ids_both INTERVAL DAY(5) TO SECOND(4),
  -- Float with various precisions
  float_default FLOAT,
  float_1 FLOAT(1),
  float_63 FLOAT(63),
  float_126 FLOAT(126),
  -- Number with various precisions and scales
  num_default NUMBER,
  num_p NUMBER(5),
  num_ps NUMBER(10, 2),
  num_p_neg_s NUMBER(10, -2),
  -- Character types with sizes
  char_default CHAR,
  char_sized CHAR(50),
  varchar2_sized VARCHAR2(255),
  nchar_default NCHAR,
  nchar_sized NCHAR(50),
  nvarchar2_sized NVARCHAR2(255),
  -- Raw with size
  raw_sized RAW(200),
  -- UROWID with size
  urowid_default UROWID,
  urowid_sized UROWID(100)
);

EXIT;

